﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Specialized;
using System.Data.SqlClient;
using Tools.SqlServer;
namespace Tools.Other
{
   
    public static class SqlWhereCreater
    {
        public static void ToSqlFilter(NameValueCollection nv, string Item, bool isIncludeItem, List<SqlParameter> lstParam, out string sql, string prefix = "", string valueType = "")
        {

            string[] keys = nv.AllKeys;

            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < keys.Length; i++)
            {
                if (string.IsNullOrEmpty(keys[i]))
                {
                    continue;
                }
                string field = keys[i].ToUpper().Trim();
                //string a = Item.ToUpper();
                //bool kk = a.Contains(field);
                string[] items = Item.ToUpper().Split('|');
                bool isInclude = items.Contains(field) == isIncludeItem;
                //bool isInclude = Item.ToUpper().Contains(field) == isIncludeItem;

                if (isInclude)
                {
                    string value = nv[keys[i]].Trim();
                    if (!string.IsNullOrEmpty(value))
                    {
                        //if (sb.Length > 1)
                        //{
                        //    sb.Append(" AND ");
                        //}
                        sb.Append(" AND ");

                        if (field.Contains("RANGETIME"))//日期型日期
                        {
                            sqlRangeTime(keys[i], value, lstParam, sb, prefix);

                        }
                        else if (field.Contains("RANGE"))//字符型日期
                        {
                            sqlRange(keys[i], value, lstParam, sb, prefix);

                        }
                        else if (value.Contains("☆"))//多选查询
                        {
                            sqlMultiValue(field, value, lstParam, sb, prefix, valueType);


                        }
                        else
                        {
                            sqlValue(field, value, lstParam, sb, prefix, valueType);

                        }


                    }

                }

            }
            sql = sb.ToString();
        }


        public static void sqlRange(string key, string value, List<SqlParameter> lstParam, StringBuilder sb, string prefix = "")
        {
            string[] fieldData = key.Split('_');
            string trueField = fieldData[0].ToUpper();
            string range = fieldData[1].ToUpper();

            string atField = "@" + key;
            lstParam.Add(SqlHelper.getParameter(atField, value.Replace("-", "")));
            if (range.Equals("RANGE1"))
            {

                sb.AppendFormat("SUBSTRING(REPLACE({0},'-',''),1,8) >= {1}", prefix + trueField, atField);

            }
            if (range.Equals("RANGE2"))
            {
                sb.AppendFormat("SUBSTRING(REPLACE({0},'-',''),1,8) <= {1}", prefix + trueField, atField);
            }
        }

        public static void sqlRangeTime(string key, string value, List<SqlParameter> lstParam, StringBuilder sb, string prefix = "")
        {
            string[] fieldData = key.Split('_');
            string trueField = fieldData[0].ToUpper();
            string range = fieldData[1].ToUpper();
            if (!string.IsNullOrEmpty(prefix))
            {
                prefix = prefix + ".";
            }
            string atField = "@" + key;
            lstParam.Add(SqlHelper.getParameter(atField, value.Replace("-", "")));
            if (range.Equals("RANGETIME1"))
            {

                sb.AppendFormat("CONVERT(varchar(100), {0}, 112) >= {1}", prefix + trueField, atField);

            }
            if (range.Equals("RANGETIME2"))
            {
                sb.AppendFormat("CONVERT(varchar(100), {0}, 112) <= {1}", prefix + trueField, atField);
            }
        }

        public static void sqlValue(string field, string value, List<SqlParameter> lstParam, StringBuilder sb, string prefix = "", string valueType = "")
        {
            if (!string.IsNullOrEmpty(prefix))
            {
                prefix = prefix + ".";
            }
            string atField = "@" + field;

            if (valueType.Equals("int"))
            {
                sb.AppendFormat("{0} = {1}", prefix + field, atField);
                lstParam.Add(SqlHelper.getParameter(atField, Convert.ToInt32(value)));
            }
            else if (valueType.Equals("decimal"))
            {
                sb.AppendFormat("{0} = {1}", prefix + field, atField);
                lstParam.Add(SqlHelper.getParameter(atField, Convert.ToDecimal(value)));
            }
            else if (valueType.Equals("notLike"))
            {
                sb.AppendFormat("{0} like {1}", prefix + field, atField);
                lstParam.Add(SqlHelper.getParameter(atField, value));
            }
            else if (valueType.Equals("="))
            {
                sb.AppendFormat("{0} = {1}", prefix + field, atField);
                lstParam.Add(SqlHelper.getParameter(atField, value));
            }
            else
            {
                sb.AppendFormat("{0} like {1}", prefix + field, atField);
                lstParam.Add(SqlHelper.getParameter(atField, "%" + value + "%"));
            }

        }

        public static void sqlMultiValue(string field, string value, List<SqlParameter> lstParam, StringBuilder sb, string prefix = "", string valueType = "")
        {
            if (!string.IsNullOrEmpty(prefix))
            {
                prefix = prefix + ".";
            }
            string[] values = value.Split('☆');
            //string trueValue = "";
            string atFields = "";
            for (int j = 0; j < values.Length; j++)
            {
                string atField = "@" + field + j.ToString();
                if (!values[j].Equals(""))
                {
                    if (!atFields.Equals(""))
                    {

                        atFields += ",";
                    }
                    atFields += atField;
                }
                else
                {
                    continue;
                }


                if (valueType.Equals("int"))
                {
                    lstParam.Add(SqlHelper.getParameter(atField, Convert.ToInt32(values[j])));
                }
                else if (valueType.Equals("decimal"))
                {
                    lstParam.Add(SqlHelper.getParameter(atField, Convert.ToDecimal(values[j])));
                }
                else
                {
                    lstParam.Add(SqlHelper.getParameter(atField, values[j]));
                }
            }
            sb.AppendFormat("{0} in ({1})", prefix + field, atFields);



        }

        /// <summary>
        /// whereSql = Rapoo.Data.common.SqlCreater.addWhereSql("kunnr", kunnr, lstSqlParameter, "h");
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <param name="lstSqlParameter"></param>
        /// <param name="prefix"></param>
        /// <returns></returns>
        public static string addWhereSql(string key, object value, List<SqlParameter> lstSqlParameter, string prefix = "")
        {
            if (!string.IsNullOrEmpty(prefix))
            {
                prefix = prefix + ".";
            }
            if (!prefix.Contains("."))
            {
                prefix += ".";
            }
            string whereSql = string.Format(@" and {0}{1}=@{1} ", prefix, key);
            lstSqlParameter.Add(new SqlParameter("@" + key, value));
            return whereSql;
        }
    }
}
